﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Diagnostics;
using System.Linq;
using System.Reflection;
using AChance.Func;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Query;
using Microsoft.EntityFrameworkCore.Query.SqlExpressions;
using Microsoft.EntityFrameworkCore.Storage;

namespace AChance.Dal.Func
{
    public static class DalExtensions
    {
        #region String
        /// <summary>
        /// 返回此字符串转换为需要进行Sql参数化的副本
        /// </summary>
        public static string ToSqlArg(this string caller)
        {
            return DalConst.SqlArg.PREFIX + caller + DalConst.SqlArg.SUFFIX;
        }
        /// <summary>
        /// 返回此字符串转换为原始Sql语句的副本
        /// </summary>
        public static string ToRawSql(this string caller, DbParameterCollection args = null)
        {
            var result = caller.Trim().Replace(DalConst.SqlArg.PREFIX, "'").Replace(DalConst.SqlArg.SUFFIX, "'");
            if (!args.IsNullOrEmpty())
                foreach (DbParameter arg in args)
                {
                    string argName = (arg.ParameterName.StartsWith("@") ? "" : "@") + arg.ParameterName;
                    switch (arg.DbType)
                    {
                        case DbType.String:
                            result = result.Replace(argName, $"'{arg.Value}'");
                            break;
                        default:
                            result = result.Replace(argName, arg.Value.ToString());
                            break;
                    }
                }
            return result;
        }
        #endregion

        #region IQueryable
        private static (string Sql, IReadOnlyDictionary<string, object> Args) ToSqlArgs<TEntity>(this IQueryable<TEntity> queryable) where TEntity : class
        {
            var enumerator = queryable.Provider.Execute<IEnumerable<TEntity>>(queryable.Expression).GetEnumerator();
            var commandCache = GetValue(enumerator, "_relationalCommandCache");
            var selectExpression = GetValueByType<SelectExpression>(commandCache, "_selectExpression");
            var factory = GetValueByType<IQuerySqlGeneratorFactory>(commandCache, "_querySqlGeneratorFactory");
            var command = factory.Create().GetCommand(selectExpression);
            var queryContext = GetValueByType<RelationalQueryContext>(enumerator, "_relationalQueryContext");
            return (command.CommandText, queryContext.ParameterValues);

            static object GetValue(object obj, string privateField) => obj?.GetType().GetField(privateField, BindingFlags.Instance | BindingFlags.NonPublic)?.GetValue(obj);
            static T GetValueByType<T>(object obj, string privateField) => (T)obj?.GetType().GetField(privateField, BindingFlags.Instance | BindingFlags.NonPublic)?.GetValue(obj);
        }
        public static string ToSql<TEntity>(this IQueryable<TEntity> queryable) where TEntity : class
        {
            var sqlArgs = queryable.ToSqlArgs();
            var result = sqlArgs.Sql.Trim();
            if (!sqlArgs.Args.IsNullOrEmpty())
                foreach (var arg in sqlArgs.Args)
                {
                    string argName = (arg.Key.StartsWith("@") ? "" : "@") + arg.Key;
                    switch (arg.Value)
                    {
                        case null:
                            result = result.Replace(argName, "NULL");
                            break;
                        case string value:
                            result = result.Replace(argName, value.ToSqlArg());
                            break;
                        default:
                            result = result.Replace(argName, arg.Value.ToString());
                            break;
                    }
                }
            return result;
        }
        public static string ToRawSql<TEntity>(this IQueryable<TEntity> queryable) where TEntity : class
        {
            var sqlArgs = queryable.ToSqlArgs();
            var result = sqlArgs.Sql.Trim().Replace(DalConst.SqlArg.PREFIX, "'").Replace(DalConst.SqlArg.SUFFIX, "'");
            if (!sqlArgs.Args.IsNullOrEmpty())
                foreach (var arg in sqlArgs.Args)
                {
                    string argName = (arg.Key.StartsWith("@") ? "" : "@") + arg.Key;
                    switch (arg.Value)
                    {
                        case null:
                            result = result.Replace(argName, "NULL");
                            break;
                        case string value:
                            result = result.Replace(argName, $"'{value}'");
                            break;
                        default:
                            result = result.Replace(argName, arg.Value.ToString());
                            break;
                    }
                }
            return result;
        }
        #endregion

        #region DatabaseFacade
        private static DbCommand CreateDbCommand(DatabaseFacade databaseFacade, string sql, params object[] parameters)
        {
            var dbConnection = databaseFacade.GetDbConnection();
            if (dbConnection.State == ConnectionState.Closed)
                dbConnection.Open();
            var dbCommand = dbConnection.CreateCommand();
            dbCommand.Transaction = databaseFacade.CurrentTransaction?.GetDbTransaction();
            dbCommand.CommandTimeout = 1000;
            dbCommand.CommandText = sql;
            dbCommand.Parameters.AddRange(parameters);
            return dbCommand;
        }
        public static DataTable ExecuteReader(this DatabaseFacade databaseFacade, string sql, params object[] parameters)
        {
            DbCommand dbCommand = null;
            DbDataReader dbDataReader = null;
            //开始计时
            Stopwatch stopwatch = Stopwatch.StartNew();
            try
            {
                dbCommand = CreateDbCommand(databaseFacade, sql, parameters);
                dbDataReader = dbCommand.ExecuteReader();
                var result = new DataTable();
                result.Load(dbDataReader);
                return result;
            }
            catch (Exception ex)
            {
                FunLogs.LogErrorForSqlExec(ex, sql.ToRawSql(dbCommand?.Parameters));
                throw ex;
            }
            finally
            {
                dbDataReader?.Close();
                dbCommand?.Cancel();
                //结束计时
                stopwatch.Stop();
                if (stopwatch.Elapsed.TotalMilliseconds > 3000)
                    FunLogs.LogWarnForSqlExec(sql.ToRawSql(dbCommand?.Parameters), stopwatch.Elapsed);
            }
        }
        public static object ExecuteScalar(this DatabaseFacade databaseFacade, string sql, params object[] parameters)
        {
            DbCommand dbCommand = null;
            //开始计时
            Stopwatch stopwatch = Stopwatch.StartNew();
            try
            {
                dbCommand = CreateDbCommand(databaseFacade, sql, parameters);
                return dbCommand.ExecuteScalar();
            }
            catch (Exception ex)
            {
                FunLogs.LogErrorForSqlExec(ex, sql.ToRawSql(dbCommand?.Parameters));
                throw ex;
            }
            finally
            {
                dbCommand?.Cancel();
                //结束计时
                stopwatch.Stop();
                if (stopwatch.Elapsed.TotalMilliseconds > 3000)
                    FunLogs.LogWarnForSqlExec(sql.ToRawSql(dbCommand?.Parameters), stopwatch.Elapsed);
            }
        }
        #endregion

    }
}
